with first_code as (select *
                    from (select *,
                                 row_number() over (partition by code order by flg desc) as rn
                          from (select code                              --code
                                     , name                              --建包名称
                                     , type
                                     , dest_center_code dest_center_code --目的中心名称
                                     , dest_center_name dest_center_name --目的中心名称
                                     , center_code      center_code      --末端机构编码
                                     , center_name      center_name      --末端机构名称
                                     , 2 as             flg
                                from jms_dim.dim_lmdm_sys_first_code t
                                where t.is_enable = '1'
                                  and t.is_delete = '1'
                                union all
                                select express_center_short_code as code
                                     , t.name
                                     , type
                                     , express_center_code          dest_center_code
                                     , t1.name                      dest_center_name
                                     , consolidation_code           center_code
                                     , consolidation_name           center_name
                                     , 1                         as flg
                                from jms_dim.dim_yl_sys_package_different_base t
                                         left join jms_dim.dim_network_whole_massage t1 on t.express_center_code = t1.code) t) t
                    where rn = 1),
---二段码
     second_code as (select *
                     from (select *
                                , row_number() over (partition by code,center_code order by create_time desc) as row_id
                           from jms_dim.dim_lmdm_sys_second_code
                           where is_enable = 1
                             and is_delete = 1) tmp
                     where row_id = 1),
     second_code_dup as (select code
                         from (select code, count(1) num
                               from second_code
                               group by code) sys_code
                         where sys_code.num > 1),
     second_code_one as (select a.*
                         from second_code a
                                  left anti
                                  join second_code_dup b on a.code = b.code),
     second_code_two as (select a.*
                         from second_code a left semi join second_code_dup b on a.code = b.code),
     second_first as (select /*+ BROADCAST(second_code) */  second_code.*,first_code.code first_code
                      from second_code_two second_code
                               left join first_code on first_code.dest_center_code = second_code.center_code
                                                         ),
     all_second as (
     select * from (
        select *,row_number() over(partition by code order by type ) rn from (
          select code, null first_code,network_code ,1 type from second_code_one
          union all
          select code,first_code,network_code, 2 type from second_first where first_code is not null
        ) detail
        ) rn_detail
     )

insert overwrite table bidefault.dm_prescription_reach_pre_details_dt partition(dt)
select
taking_date
,island
,waybill_no
,plan_sign_day
,island_code
,island_name
,customer_code
,customer_name
,send_time
,start_manage_regional_code
,start_manage_regional_name
,start_subordinate_agent_code
,start_subordinate_agent_name
,start_parent_network_code
,start_parent_network_name
,start_regional_id
,start_regional_desc
,start_provider_id
,start_provider_desc
,start_city_id
,start_city_desc
,start_area_id
,start_area_desc
,start_pick_network_code
,start_pick_network_name
,sign_manage_regional_code
,sign_manage_regional_name
,sign_subordinate_agent_code
,sign_subordinate_agent_name
,sign_parent_network_code
,sign_parent_network_name
,sign_regional_id
,sign_regional_desc
,sign_provider_id
,sign_provider_desc
,sign_city_id
,sign_city_desc
,sign_area_id
,sign_area_desc
,sign_network_code
,sign_network_name
,taking_time
,start_center_code
,start_center_name
,start_centerarriva_departure_time
,start_centerarriva_time
,start_centersend_departure_time
,departure_task
,sign_center_code
,sign_center_name
,sign_centersend_departure_time
,sign_centerarrival_departure_time
,sign_centersend_time
,cargo_time
,arrival_shipment_name
,pres_sign_time
,real_sign_time
,is_pres_sign
,is_real_sign
,is_delivery_delay
,is_in_delay
,is_out_delay
,aging_sign_count_type
,is_trunk_delay
,districtprescription
,is_real_standard
,is_pres_standard
,plan_sign_date
,economic_circle_type
,platform_effect
,is_platform_real_standard
,is_platform_pres_standard
,platform_effect_type
,platform_name
,plan_platform_date
,mountain_platform_effect
,mountain_is_platform_real_standard
,mountain_is_platform_pres_standard
,mountain_platform_effect_type
,mountain_platform_name
,plan_mountain_platform_date
,free_platform_effect
,free_is_platform_real_standard
,free_is_platform_pres_standard
,free_platform_effect_type
,free_platform_name
,plan_free_platform_date
,jd_platform_effect
,jd_is_platform_real_standard
,jd_is_platform_pres_standard
,jd_platform_effect_type
,jd_platform_name
,plan_jd_platform_date
,cainiao_platform_effect
,cainiao_is_platform_real_standard
,cainiao_is_platform_pres_standard
,cainiao_platform_effect_type
,cainiao_platform_name
,plan_cainiao_platform_date
,cainiao_pro_platform_effect
,cainiao_pro_is_platform_real_standard
,cainiao_pro_is_platform_pres_standard
,cainiao_pro_platform_effect_type
,cainiao_pro_platform_name
,plan_cainiao_pro_platform_date
,express_type_code
,express_type_name
,collect_send_in_time
,start_unloading_shipment_no
,end_unloading_shipment_no
,route_send_time
,cainiao_slower_pre
,cainiao_pro_taking_slot
,order_mark_field_name
,extra_time
,datediff(date(plan_cainiao_pro_platform_date),date(real_sign_time) ) cainiao_pro_real_effect_diff
,datediff(date(plan_cainiao_pro_platform_date),date(pres_sign_time) ) cainiao_pro_pres_effect_diff
,is_taking_off
,mountain_pro_platform_effect
,mountain_pro_is_platform_real_standard
,mountain_pro_is_platform_pres_standard
,mountain_pro_platform_effect_type
,mountain_pro_platform_name
,plan_mountain_pro_platform_date
,dt
from (
select /*+ BROADCAST(first_code) */
date(detail.scantime) as taking_date -- " 揽收日期",
,case when detail.order_source_code='D56' then 1
             when detail.ordersource_code='D02' then 2
             when detail.ordersource_code in ('D60','D09','D69') then 3
             when detail.ordersource_code='1085' then 4
             when detail.ordersource_code='1133' then 5
             else 6 end island  --订单来源 1oppo电商 2VIP系统 3桃花岛 4抖音 5快手 6其它
,detail.waybill_no as waybill_no --" 运单号",
,date_add(date(detail.scantime),
      valid_data.search_type + if(detail.scantime >  valid_data.max_end_hour,1,0)
) as plan_sign_day  -- 规划应签收日期
,detail.ordersource_code as island_code-- "订单来源code",
,detail.ordersource_name as island_name-- "订单来源名称",
,detail.customer_code as customer_code  --" 客户编号",
,detail.customer_name as customer_name --" 客户名称",
,first_network_send_later_time as send_time --发件时间
,start_network.manage_code as start_manage_regional_code --" 始发管理大区code",
,start_network.manage_name as start_manage_regional_name --" 始发管理大区name",
,start_network.agent_code as start_subordinate_agent_code-- " 始发代理区code",
,start_network.agent_name as start_subordinate_agent_name-- " 始发代理区name",
,start_network.fran_code as start_parent_network_code-- " 始发加盟商code",
,start_network.fran_name as start_parent_network_name-- " 始发加盟商name",
,start_network.regional_id as start_regional_id-- " 始发大区id",
,start_network.regional_desc as start_regional_desc-- " 始发大区name",
,start_network.provider_id as start_provider_id-- " 始发省份id",
,start_network.provider_desc as start_provider_desc-- " 始发省份name",
,start_network.city_id as start_city_id-- " 始发城市id",
,start_network.city_desc as start_city_desc-- " 始发城市name",
,start_network.area_id as start_area_id-- " 始发区id",
,start_network.area_desc as start_area_desc-- " 始发区name",
,start_network.code as start_pick_network_code-- " 始发网点code",
,start_network.name as start_pick_network_name-- " 始发网点name",
,if(cast(detail.reback_apply_type_code as int)=4,aim_network.manage_code,sign_network.manage_code) as sign_manage_regional_code -- "签收管理大区code",
,if(cast(detail.reback_apply_type_code as int)=4,aim_network.manage_name,sign_network.manage_name) as sign_manage_regional_name -- "签收管理大区name",
,if(cast(detail.reback_apply_type_code as int)=4,aim_network.agent_code,sign_network.agent_code) as sign_subordinate_agent_code -- " 签收代理区code",
,if(cast(detail.reback_apply_type_code as int)=4,aim_network.agent_name,sign_network.agent_name) as sign_subordinate_agent_name -- " 签收代理区name",
,if(cast(detail.reback_apply_type_code as int)=4,aim_network.fran_code,sign_network.fran_code) as sign_parent_network_code -- " 签收加盟商code",
,if(cast(detail.reback_apply_type_code as int)=4,aim_network.fran_name,sign_network.fran_name) as sign_parent_network_name -- " 签收加盟商name",
,if(cast(detail.reback_apply_type_code as int)=4,aim_network.regional_id,sign_network.regional_id) as sign_regional_id -- " 签收大区id",
,if(cast(detail.reback_apply_type_code as int)=4,aim_network.regional_desc,sign_network.regional_desc) as sign_regional_desc -- " 签收大区name",
,if(cast(detail.reback_apply_type_code as int)=4,aim_network.provider_id,sign_network.provider_id) as sign_provider_id -- " 签收省份id",
,if(cast(detail.reback_apply_type_code as int)=4,aim_network.provider_desc,sign_network.provider_desc) as sign_provider_desc -- " 签收省份name",
,if(cast(detail.reback_apply_type_code as int)=4,aim_network.city_id,sign_network.city_id) as sign_city_id -- " 签收城市id",
,if(cast(detail.reback_apply_type_code as int)=4,aim_network.city_desc,sign_network.city_desc) as sign_city_desc -- " 签收城市name",
,if(cast(detail.reback_apply_type_code as int)=4,aim_network.area_id,sign_network.area_id) as sign_area_id -- " 签收区id",
,if(cast(detail.reback_apply_type_code as int)=4,aim_network.area_desc,sign_network.area_desc) as sign_area_desc -- " 签收区name",
,if(cast(detail.reback_apply_type_code as int)=4,aim_network.code,sign_network.code) as sign_network_code -- " 签收网点code",
,if(cast(detail.reback_apply_type_code as int)=4,aim_network.name,sign_network.name) as sign_network_name -- " 签收网点name",
,detail.scantime as taking_time --" 揽收时间",
,detail.first_center_network_code as start_center_code --" 始发转运中心code",
,start_center_code.name as start_center_name --" 始发转运中心name",
,detail.first_center_unloading_arrival_actual_arrival_time as start_centerarriva_departure_time --" 始发转运中心到车时间",
,detail.first_center_arrival_early_time as start_centerarriva_time --" 始发转运到件时间",
,detail.first_center_loading_send_actual_departure_time as start_centersend_departure_time --"始发转运发车时间",
,detail.first_center_loading_send_shipment_no as departure_task --" 始发发车任务单号",
,if(cast(detail.reback_apply_type_code as int)<>4 and detail.pres_sign_time is not null,detail.end_center_network_code,first_code.center_code) as sign_center_code -- " 目的转运中心code",
,if(cast(detail.reback_apply_type_code as int)<>4 and detail.pres_sign_time is not null,sign_center_code.name,first_code.center_name) as sign_center_name -- " 目的转运中心name",
,is_in_delay.send_time as sign_centersend_departure_time -- " 目的中心发车扫描时间",
,detail.sign_centerarrival_departure_time as sign_centerarrival_departure_time -- " 目的中心到车时间",
,detail.end_center_send_later_time as sign_centersend_time -- " 目的中心发件扫描时间",
,is_in_delay.cargo_time as cargo_time --应散货时间
,is_in_delay.arrival_shipment_name as arrival_shipment_name --目的中心到车线路名称
,detail.pres_sign_time as pres_sign_time --" 时效签收时间",
,detail.real_sign_time as real_sign_time --" 实际签收时间",
,case when detail.pres_sign_time is not null then 1 else 0 end as is_pres_sign  -- 时效签收
,case when detail.real_sign_time is not null then 1 else 0 end as is_real_sign  -- 实际签收
,is_delivery_delay.hand_over_intime as is_delivery_delay --"交件是否准点 0否1是",
,is_in_delay.is_timely as is_in_delay --"进港是否及时 0否1是",
,is_out_delay.is_timely as is_out_delay --"出港转运是否及时 0否1是",
,case when aging_sign.aging_sign_count_type is not null  and aging_sign_count_type > 20 then 0
      when aging_sign.aging_sign_count_type is not null  and aging_sign_count_type <= 20 then 1
else NULL end  as aging_sign_count_type --时效签收是否准点
,case when detail.first_center_loading_send_planned_departure_time is not null and
           detail.first_center_loading_send_actual_departure_time is not null and
           detail.first_center_loading_send_planned_departure_time <  detail.first_center_loading_send_actual_departure_time then 0
      when detail.first_center_loading_send_planned_departure_time is not null and
           detail.first_center_loading_send_actual_departure_time is not null and
           detail.first_center_loading_send_planned_departure_time >= detail.first_center_loading_send_actual_departure_time then 1
else NULL end  as is_trunk_delay --"干线班次是否及时 0否1是",
,valid_data.search_type + if(detail.scantime >  valid_data.max_end_hour,1,0) as districtprescription   -- 区县时效 截单后揽收 + 1
,case when detail.real_sign_time is null and datediff(current_date(),date(detail.scantime))>
      valid_data.search_type + if(detail.scantime >  valid_data.max_end_hour,1,0) then 0
      when detail.real_sign_time is null and datediff(current_date(),date(detail.scantime))<=
      valid_data.search_type + if(detail.scantime >  valid_data.max_end_hour,1,0) then null
      when detail.real_sign_time is not null and datediff(detail.real_sign_time,date(detail.scantime))<=
      valid_data.search_type + if(detail.scantime >  valid_data.max_end_hour,1,0) then 1
      when detail.real_sign_time is not null and datediff(detail.real_sign_time,date(detail.scantime))>
      valid_data.search_type + if(detail.scantime >  valid_data.max_end_hour,1,0) then 0
end as is_real_standard  -- 实际是否达标  1是0否
,case when detail.pres_sign_time is null and datediff(current_date(),date(detail.scantime))>
      valid_data.search_type + if(detail.scantime >  valid_data.max_end_hour,1,0) then 0
      when detail.pres_sign_time is null and datediff(current_date(),date(detail.scantime))<=
      valid_data.search_type + if(detail.scantime >  valid_data.max_end_hour,1,0) then null
      when detail.pres_sign_time is not null and datediff(detail.pres_sign_time,date(detail.scantime))<=
      valid_data.search_type + if(detail.scantime >  valid_data.max_end_hour,1,0) then 1
      when detail.pres_sign_time is not null and datediff(detail.pres_sign_time,date(detail.scantime))>
      valid_data.search_type + if(detail.scantime >  valid_data.max_end_hour,1,0) then 0
end as is_pres_standard  -- 时效是否达标  1是0否
,concat(date_add(date(detail.scantime),
        valid_data.search_type + if(detail.scantime >  valid_data.max_end_hour,1,0)),' 23:59:59') as plan_sign_date  -- 规划应签收日期
,case when start_network.city_id in ('440600','440300','440100','441300','441900','440400','442000','441200','440700')
and sign_network.city_id in ('440600','440300','440100','441300','441900','440400','442000','441200','440700') then 1
when start_network.city_id in ('330100','310100','331000','330500','330200','320500','320200','330700','321200','320600','330600','320900','321000',
'320100','330900','321100','330400','320400','340100','340200','340500','340700','340800','341100','341700','341800')
and sign_network.city_id in ('330100','310100','331000','330500','330200','320500','320200','330700','321200','320600','330600','320900','321000',
'320100','330900','321100','330400','320400','340100','340200','340500','340700','340800','341100','341700','341800') then 2
when start_network.city_id in ('110100','120100','130100','130200','130400','130600','130900','130500','131000','130800','130700','131100','130300')
and sign_network.city_id in ('110100','120100','130100','130200','130400','130600','130900','130500','131000','130800','130700','131100','130300') then 3
else NULL end as economic_circle_type -- 经济圈名称 1珠三角 2长三角 3京津冀 空值不属于经济圈
,land_config.platform_effect  -- 桃花岛平台时效
,case when detail.real_sign_time is null and datediff(current_date(),date(detail.scantime))>land_config.platform_effect then 0
  when detail.real_sign_time is null and datediff(current_date(),date(detail.scantime))<=land_config.platform_effect then null
  when detail.real_sign_time is not null and datediff(detail.real_sign_time,date(detail.scantime))<=land_config.platform_effect then 1
  when detail.real_sign_time is not null and datediff(detail.real_sign_time,date(detail.scantime))>land_config.platform_effect then 0
  end as is_platform_real_standard  -- 桃花岛平台实际是否达标  1是0否
 ,case when detail.pres_sign_time is null and datediff(current_date(),date(detail.scantime))>land_config.platform_effect then 0
 when detail.pres_sign_time is null and datediff(current_date(),date(detail.scantime))<=land_config.platform_effect then null
 when detail.pres_sign_time is not null and datediff(detail.pres_sign_time,date(detail.scantime))<=land_config.platform_effect then 1
 when detail.pres_sign_time is not null and datediff(detail.pres_sign_time,date(detail.scantime))>land_config.platform_effect then 0
 end as is_platform_pres_standard  -- 桃花岛平台时效是否达标  1是0否
,case when length(land_config.platform_effect)>0 then 1 else 0 end as platform_effect_type  -- 是否桃花岛平台时效 1是0否
,land_config.platform_name  --桃花岛平台时效名称
,concat(date_add(date(detail.scantime),cast(land_config.platform_effect as int)),' 23:59:59') as plan_platform_date  -- 桃花岛规划应签收日期
,mountain_config.platform_effect as mountain_platform_effect  -- 紫金山平台时效
,case when detail.real_sign_time is null and datediff(current_date(),date(detail.scantime))>mountain_config.platform_effect then 0
  when detail.real_sign_time is null and datediff(current_date(),date(detail.scantime))<=mountain_config.platform_effect then null
  when detail.real_sign_time is not null and datediff(detail.real_sign_time,date(detail.scantime))<=mountain_config.platform_effect then 1
  when detail.real_sign_time is not null and datediff(detail.real_sign_time,date(detail.scantime))>mountain_config.platform_effect then 0
  end as mountain_is_platform_real_standard  -- 紫金山平台实际是否达标  1是0否
,case when detail.pres_sign_time is null and datediff(current_date(),date(detail.scantime))>mountain_config.platform_effect then 0
  when detail.pres_sign_time is null and datediff(current_date(),date(detail.scantime))<=mountain_config.platform_effect then null
  when detail.pres_sign_time is not null and datediff(detail.pres_sign_time,date(detail.scantime))<=mountain_config.platform_effect then 1
  when detail.pres_sign_time is not null and datediff(detail.pres_sign_time,date(detail.scantime))>mountain_config.platform_effect then 0
  end as mountain_is_platform_pres_standard  -- 紫金山平台时效是否达标  1是0否
,case when length(mountain_config.platform_effect)>0 then 1 else 0 end as mountain_platform_effect_type  -- 是否紫金山平台时效 1是0否
,mountain_config.platform_name as mountain_platform_name --紫金山平台时效名称
,concat(date_add(date(detail.scantime),cast(mountain_config.platform_effect as int)),' 23:59:59') as plan_mountain_platform_date  -- 紫金山规划应签收日期
,free_config.platform_effect as free_platform_effect -- 逍遥锋平台时效
,case when detail.real_sign_time is null and datediff(current_date(),date(detail.scantime))>free_config.platform_effect then 0
 when detail.real_sign_time is null and datediff(current_date(),date(detail.scantime))<=free_config.platform_effect then null
 when detail.real_sign_time is not null and datediff(detail.real_sign_time,date(detail.scantime))<=free_config.platform_effect then 1
 when detail.real_sign_time is not null and datediff(detail.real_sign_time,date(detail.scantime))>free_config.platform_effect then 0
 end as free_is_platform_real_standard  -- 逍遥锋平台实际是否达标  1是0否
 ,case when detail.pres_sign_time is null and datediff(current_date(),date(detail.scantime))>free_config.platform_effect then 0
  when detail.pres_sign_time is null and datediff(current_date(),date(detail.scantime))<=free_config.platform_effect then null
  when detail.pres_sign_time is not null and datediff(detail.pres_sign_time,date(detail.scantime))<=free_config.platform_effect then 1
  when detail.pres_sign_time is not null and datediff(detail.pres_sign_time,date(detail.scantime))>free_config.platform_effect then 0
  end as free_is_platform_pres_standard  -- 逍遥锋平台时效是否达标  1是0否
,case when length(free_config.platform_effect)>0 then 1 else 0 end as free_platform_effect_type  -- 是否逍遥锋平台时效 1是0否
,free_config.platform_name as free_platform_name --逍遥锋平台时效名称
,concat(date_add(date(detail.scantime),cast(free_config.platform_effect as int)),' 23:59:59') as plan_free_platform_date  -- 逍遥锋规划应签收日期
,jd_config.platform_effect as jd_platform_effect -- 京东平台时效
,case when detail.real_sign_time is null and now()>concat(date_add(date(detail.scantime),cast(jd_config.platform_effect as int)),substring(detail.scantime,11,9)) then 0
  when detail.real_sign_time is null and now()<=concat(date_add(date(detail.scantime),cast(jd_config.platform_effect as int)),substring(detail.scantime,11,9)) then null
  when detail.real_sign_time is not null and detail.real_sign_time<=concat(date_add(date(detail.scantime),cast(jd_config.platform_effect as int)),substring(detail.scantime,11,9)) then 1
  when detail.real_sign_time is not null and detail.real_sign_time>concat(date_add(date(detail.scantime),cast(jd_config.platform_effect as int)),substring(detail.scantime,11,9)) then 0
  end as jd_is_platform_real_standard  -- 京东平台实际是否达标  1是0否
 ,case when detail.pres_sign_time is null and now()>concat(date_add(date(detail.scantime),cast(jd_config.platform_effect as int)),substring(detail.scantime,11,9)) then 0
  when detail.pres_sign_time is null and now()<=concat(date_add(date(detail.scantime),cast(jd_config.platform_effect as int)),substring(detail.scantime,11,9)) then null
  when detail.pres_sign_time is not null and detail.pres_sign_time<=concat(date_add(date(detail.scantime),cast(jd_config.platform_effect as int)),substring(detail.scantime,11,9)) then 1
  when detail.pres_sign_time is not null and detail.pres_sign_time>concat(date_add(date(detail.scantime),cast(jd_config.platform_effect as int)),substring(detail.scantime,11,9)) then 0
  end as jd_is_platform_pres_standard  -- 京东平台时效是否达标  1是0否
,case when length(jd_config.platform_effect)>0 then 1 else 0 end as jd_platform_effect_type  -- 是否京东平台时效 1是0否
,jd_config.platform_name as jd_platform_name --京东平台时效名称
,concat(date_add(date(detail.scantime),cast(jd_config.platform_effect as int)),substring(detail.scantime,11,9)) as plan_jd_platform_date  -- 京东规划应签收日期
,cainiao_config.platform_effect as cainiao_platform_effect --  菜鸟平台时效
,case when detail.real_sign_time is null and datediff(current_date(),date(detail.scantime))>cainiao_config.platform_effect then 0
 when detail.real_sign_time is null and datediff(current_date(),date(detail.scantime))<=cainiao_config.platform_effect then null
 when detail.real_sign_time is not null and datediff(detail.real_sign_time,date(detail.scantime))<=cainiao_config.platform_effect then 1
 when detail.real_sign_time is not null and datediff(detail.real_sign_time,date(detail.scantime))>cainiao_config.platform_effect then 0
 end as cainiao_is_platform_real_standard  -- 菜鸟平台实际是否达标  1是0否
 ,case when detail.pres_sign_time is null and datediff(current_date(),date(detail.scantime))>cainiao_config.platform_effect then 0
  when detail.pres_sign_time is null and datediff(current_date(),date(detail.scantime))<=cainiao_config.platform_effect then null
  when detail.pres_sign_time is not null and datediff(detail.pres_sign_time,date(detail.scantime))<=cainiao_config.platform_effect then 1
  when detail.pres_sign_time is not null and datediff(detail.pres_sign_time,date(detail.scantime))>cainiao_config.platform_effect then 0
  end as cainiao_is_platform_pres_standard  -- 菜鸟平台时效是否达标  1是0否
,case when length(cainiao_config.platform_effect)>0 then 1 else 0 end as cainiao_platform_effect_type  -- 是否菜鸟平台时效 1是0否
,cainiao_config.platform_name as cainiao_platform_name --菜鸟平台时效名称
,concat(date_add(date(detail.scantime),cast(cainiao_config.platform_effect as int)),' 23:59:59') as plan_cainiao_platform_date  -- 菜鸟规划应签收日期
,cainiao_pro_config.platform_effect as cainiao_pro_platform_effect -- 菜鸟超时平台时效
,case when detail.real_sign_time is null and datediff(current_date(),date(detail.scantime))>cainiao_pro_config.platform_effect then 0
 when detail.real_sign_time is null and datediff(current_date(),date(detail.scantime))<=cainiao_pro_config.platform_effect then null
 when detail.real_sign_time is not null and datediff(detail.real_sign_time,date(detail.scantime))=cainiao_pro_config.platform_effect then 1
 when detail.real_sign_time is not null and datediff(detail.real_sign_time,date(detail.scantime))>cainiao_pro_config.platform_effect then 0
 end as cainiao_pro_is_platform_real_standard  -- 菜鸟超时平台实际是否达标  1是0否
 ,case when detail.pres_sign_time is null and datediff(current_date(),date(detail.scantime))>cainiao_pro_config.platform_effect then 0
 when detail.pres_sign_time is null and datediff(current_date(),date(detail.scantime))<=cainiao_pro_config.platform_effect then null
 when detail.pres_sign_time is not null and datediff(detail.pres_sign_time,date(detail.scantime))=cainiao_pro_config.platform_effect then 1
 when detail.pres_sign_time is not null and datediff(detail.pres_sign_time,date(detail.scantime))>cainiao_pro_config.platform_effect then 0
 end as cainiao_pro_is_platform_pres_standard  -- 菜鸟超时平台时效是否达标  1是0否
,case when length(cainiao_pro_config.platform_effect)>0 then 1 else 0 end as cainiao_pro_platform_effect_type  -- 是否菜鸟超时平台时效 1是0否
,cainiao_pro_config.platform_name as cainiao_pro_platform_name  --菜鸟超时平台时效名称
,concat(date_add(date(detail.scantime),cast(cainiao_pro_config.platform_effect as int)),' 23:59:59') as plan_cainiao_pro_platform_date  -- 菜鸟规划应签收日期
,detail.express_type_code
,detail.express_type_name
,collect_send.collect_send_in_time
,detail.first_center_unloading_arrival_shipment_no as start_unloading_shipment_no
,detail.end_center_unloading_arrival_shipment_no as end_unloading_shipment_no
,from_unixtime(unix_timestamp(is_out_delay.route_send_time, 'yyyy-MM-dd HH:mm:ss') - 3600, 'yyyy-MM-dd HH:mm:ss') as route_send_time
,case when cainiao_config.platform_effect is null then '--'
      when valid_data.search_type<cainiao_config.platform_effect then 2 --平台快于菜鸟
      when valid_data.search_type>cainiao_config.platform_effect then 1 --平台慢于菜鸟
      when valid_data.search_type=cainiao_config.platform_effect then 3 --相等
      else '--' end as cainiao_slower_pre
,concat(cainiao_pro_config.start_hm,'-',cainiao_pro_config.end_hm) cainiao_pro_taking_slot
,waybill_field.field_name order_mark_field_name
,null extra_time
,if(detail.scantime >  valid_data.max_end_hour,1,0) is_taking_off
,date(detail.scantime) as dt
,row_number() over(partition by detail.waybill_no
                       order by if(unix_timestamp(detail.scantime) - unix_timestamp(valid_data.end_hour) <= 0, 1, 2)
                                ,if( detail.scantime >  valid_data.max_end_hour,unix_timestamp(detail.scantime) - unix_timestamp(valid_data.end_hour), 0) desc
                                ,abs(unix_timestamp(detail.scantime) - unix_timestamp(valid_data.end_hour))
                                ,(unix_timestamp(detail.scantime) - unix_timestamp(valid_data.end_hour))
                    ) rn
,mountain_pro_config.platform_effect as mountain_pro_platform_effect -- 紫金山超长平台时效
,case when detail.real_sign_time is null and datediff(current_date(),date(detail.scantime))>mountain_pro_config.platform_effect then 0
 when detail.real_sign_time is null and datediff(current_date(),date(detail.scantime))<=mountain_pro_config.platform_effect then null
 when detail.real_sign_time is not null and datediff(detail.real_sign_time,date(detail.scantime))<=mountain_pro_config.platform_effect then 1
 when detail.real_sign_time is not null and datediff(detail.real_sign_time,date(detail.scantime))>mountain_pro_config.platform_effect then 0
 end as mountain_pro_is_platform_real_standard  -- 紫金山超长平台实际是否达标  1是0否
 ,case when detail.pres_sign_time is null and datediff(current_date(),date(detail.scantime))>mountain_pro_config.platform_effect then 0
  when detail.pres_sign_time is null and datediff(current_date(),date(detail.scantime))<=mountain_pro_config.platform_effect then null
  when detail.pres_sign_time is not null and datediff(detail.pres_sign_time,date(detail.scantime))<=mountain_pro_config.platform_effect then 1
  when detail.pres_sign_time is not null and datediff(detail.pres_sign_time,date(detail.scantime))>mountain_pro_config.platform_effect then 0
  end as mountain_pro_is_platform_pres_standard  -- 紫金山超长平台时效是否达标  1是0否
,case when length(mountain_pro_config.platform_effect)>0 then 1 else 0 end as mountain_pro_platform_effect_type  -- 是否紫金山超长平台时效 1是0否
,mountain_pro_config.platform_name as mountain_pro_platform_name --紫金山超长时效名称
,concat(date_add(date(detail.scantime),cast(mountain_pro_config.platform_effect as int)),' 23:59:59') as plan_mountain_pro_platform_date  -- 紫金山超长规划应签收日期
from
 (select /*+ BROADCAST(second_code_one),BROADCAST(second_code_two) */
   t.*
   ,nvl(second_code_one.network_code,second_code_two.network_code) waybill_dispatch_network_code
   ,nvl(sign_network_code,dispatch_network_code) sign_site_code
   ,date(t.scantime) scan_date
   from (
  select
  waybill_no,
  case when taking_early_scantime is null and warehousing_early_scantime is not null then warehousing_early_scantime
       when warehousing_early_scantime is null and taking_early_scantime is not null then taking_early_scantime
       when warehousing_early_scantime is null and taking_early_scantime is null then '1999-12-12'
       when taking_early_scantime <= warehousing_early_scantime then taking_early_scantime
       when taking_early_scantime > warehousing_early_scantime then warehousing_early_scantime
   end as scantime,
   first_code,
   second_code,
   order_source_code,
   ordersource_code,
   if(ordersource_name not in('苏宁','麦田圈','七星潭','有赞','极地湾','逍遥峰','桃花岛','紫金山','桃花岛跨境','桃花岛跨境（逆）','唯品会面单','紫金山跨境出口送仓'),'其他',ordersource_name) as ordersource_name,
   customer_code,
   customer_name,
   nvl(taking_early_scansitecode,warehousing_early_scansitecode) as pick_network_code,
   coalesce(instore_early_scansitecode,outstore_early_scansitecode,sign_later_scansitecode) as sign_network_code,
   dispatch_network_code,
   first_center_unloading_arrival_shipment_no,
   end_center_unloading_arrival_shipment_no,
   first_center_network_code,
   nvl(first_network_send_later_time,first_center_loading_later_time) as first_network_send_later_time,
   first_center_arrival_early_time,
   first_center_unloading_arrival_actual_arrival_time,
   first_center_loading_send_actual_departure_time,
   if(first_center_loading_send_shipment_type =1,first_center_loading_send_planned_departure_time,null) as first_center_loading_send_planned_departure_time,
   if(first_center_loading_send_shipment_type =1,first_center_loading_send_shipment_no,null) as first_center_loading_send_shipment_no,
   sign_later_scansitecode,
   end_center_network_code,
   end_center_arrival_early_time,
   end_center_send_later_time,
   nvl(end_center_unloading_arrival_actual_arrival_time,end_center_unloading_arrival_actual_departure_time) as sign_centerarrival_departure_time,
   coalesce(instore_early_scantime,outstore_early_scantime,sign_early_scantime) as pres_sign_time,
   reback_apply_type_code,
   express_type_code,
   express_type_name,
   first_code,
   nvl(outstore_early_scantime,sign_early_scantime) as real_sign_time
  from jms_dwd.dwd_wide_unsign_summary_waybill_dt
  where dt>= date_add(date_sub('{{ execution_date | cst_ds }}',10),-10) and dt<='{{ execution_date | cst_ds }}'
  and nvl(end_piece_type_code,99999) not in (44,5)
) t
 left join  all_second second_code_one
 on t.second_code = second_code_one.code
 and second_code_one.type = 1
 left join  all_second second_code_two
 on t.first_code = second_code_two.first_code
 and t.second_code = second_code_two.code
 and second_code_two.type = 2
where date(t.scantime) >= date_sub('{{ execution_date | cst_ds }}',10) and date(t.scantime) <= '{{ execution_date | cst_ds }}'
) detail
left join jms_dim.dim_network_whole_massage start_network on detail.pick_network_code = start_network.code
left join jms_dim.dim_network_whole_massage sign_network on detail.sign_site_code = sign_network.code
left join jms_dim.dim_network_whole_massage aim_network on detail.waybill_dispatch_network_code = aim_network.code
-- 一段码取目的中心
left join  first_code on detail.first_code=first_code.code
left join (select search_type,in_from_code,out_to_code,latest_warehousing_time_quantum
 ,dt,concat(dt,' ', start_hour, ':00') start_hour,concat(dt,' ', end_hour, ':59') end_hour,
 max(concat(dt,' ', end_hour, ':59')) over(partition by in_from_code,out_to_code,dt) max_end_hour
 from
 (select search_type,in_from_code,out_to_code,latest_warehousing_time_quantum,
  substr(latest_warehousing_time_quantum,0,5) start_hour,
  substr(latest_warehousing_time_quantum,7,5) end_hour,
  date_add(date(dt),1) dt,
  row_number() over(partition by in_from_code,out_to_code,latest_warehousing_time_quantum,dt order by search_type) rnk
  from jms_dm.dm_route_whole_node_base_dt
 where dt>=date_add(date(date_sub('{{ execution_date | cst_ds }}',10)),-1) and dt<'{{ execution_date | cst_ds }}' and latest_warehousing_time_quantum is not null and is_main_route not in (0,2) ) tt
 where rnk = 1
 ) valid_data
  on start_network.code=valid_data.in_from_code
  and if(cast(detail.reback_apply_type_code as int)=4, aim_network.code,sign_network.code)=valid_data.out_to_code
  and date(detail.scantime)=valid_data.dt
  --进港是否及时
  left join(
    select billcode,is_timely,cargo_time,arrival_shipment_name,send_time from
    (select billcode,is_timely,cargo_time,arrival_shipment_name,send_time,row_number() over(partition by billcode order by send_time desc) as rnk
    from jms_dm.dm_centersend_timerate_detail_v2_dt where dt >= date_add(date_sub('{{ execution_date | cst_ds }}',10),-2) and dt<='{{ execution_date | cst_ds }}'
    ) tt
    where tt.rnk=1
  ) is_in_delay
  on detail.waybill_no = is_in_delay.billcode
--时效签收是否准点
left join(
 select billcode,max(aging_sign_count_type) as aging_sign_count_type from jms_dm.dm_terminal_sign_detail_new_dt
  where dt >= date_add(date_sub('{{ execution_date | cst_ds }}',10),-2) and dt<='{{ execution_date | cst_ds }}' group by billcode
) aging_sign
on detail.waybill_no = aging_sign.billcode
--集散散货是否准点
left join(
 SELECT * from(
select billcode,collect_send_in_time,row_number()over(partition by billcode order by center_scantime desc,send_scan_time desc) as rnk from jms_dm.dm_collect_analysis_detail
  where dt >= date_sub('{{ execution_date | cst_ds }}',10) and dt<='{{ execution_date | cst_ds }}') where rnk =1
) collect_send
on detail.waybill_no = collect_send.billcode
  --取平台时效
left join (select * from jms_dim.dim_tab_platform_effect_config_base where platform_name='桃花岛') land_config
on start_network.city_desc=land_config.start_city
and if(cast(detail.reback_apply_type_code as int)=4,aim_network.city_desc,sign_network.city_desc)=land_config.end_city
and date(detail.scantime)>=from_unixtime(unix_timestamp(land_config.effect_start_time),'yyyy-MM-dd')
and date(detail.scantime)<date_add(from_unixtime(unix_timestamp(land_config.effect_end_time),'yyyy-MM-dd'),1)
left join (select * from jms_dim.dim_tab_platform_effect_config_base where platform_name='紫金山') mountain_config
on start_network.city_desc=mountain_config.start_city
and if(cast(detail.reback_apply_type_code as int)=4,aim_network.city_desc,sign_network.city_desc)=mountain_config.end_city
and date(detail.scantime)>=from_unixtime(unix_timestamp(mountain_config.effect_start_time),'yyyy-MM-dd')
and date(detail.scantime)<date_add(from_unixtime(unix_timestamp(mountain_config.effect_end_time),'yyyy-MM-dd'),1)
left join (select * from jms_dim.dim_tab_platform_effect_config_base where platform_name='逍遥峰') free_config
on start_network.city_desc=free_config.start_city
and if(cast(detail.reback_apply_type_code as int)=4,aim_network.city_desc,sign_network.city_desc)=free_config.end_city
and date(detail.scantime)>=from_unixtime(unix_timestamp(free_config.effect_start_time),'yyyy-MM-dd')
and date(detail.scantime)<date_add(from_unixtime(unix_timestamp(free_config.effect_end_time),'yyyy-MM-dd'),1)
left join (select * from jms_dim.dim_tab_platform_effect_config_base where platform_name='极地湾') jd_config
on start_network.city_desc=jd_config.start_city
and if(cast(detail.reback_apply_type_code as int)=4,aim_network.city_desc,sign_network.city_desc)=jd_config.end_city
and date(detail.scantime)>=from_unixtime(unix_timestamp(jd_config.effect_start_time),'yyyy-MM-dd')
and date(detail.scantime)<date_add(from_unixtime(unix_timestamp(jd_config.effect_end_time),'yyyy-MM-dd'),1)
left join (select * from jms_dim.dim_tab_platform_effect_config_base where platform_name='七星潭') cainiao_config
on start_network.city_desc=cainiao_config.start_city
and if(cast(detail.reback_apply_type_code as int)=4,aim_network.city_desc,sign_network.city_desc)=cainiao_config.end_city
and date(detail.scantime)>=from_unixtime(unix_timestamp(cainiao_config.effect_start_time),'yyyy-MM-dd')
and date(detail.scantime)<date_add(from_unixtime(unix_timestamp(cainiao_config.effect_end_time),'yyyy-MM-dd'),1)
left join (select * from jms_dim.dim_tab_platform_effect_config_base where platform_name='紫金山超长') mountain_pro_config
on start_network.city_desc=mountain_pro_config.start_city
and if(cast(detail.reback_apply_type_code as int)=4,aim_network.city_desc,sign_network.city_desc)=mountain_pro_config.end_city
and detail.scan_date>=from_unixtime(unix_timestamp(mountain_pro_config.effect_start_time),'yyyy-MM-dd')
and detail.scan_date<date_add(from_unixtime(unix_timestamp(mountain_pro_config.effect_end_time),'yyyy-MM-dd'),1)
left join (select taking_code,deliver_code,start_hm,end_hm,days platform_effect,'七星潭计划' platform_name, dt
from jms_dm.dm_cainiao_active_tn_v2_dt where dt >= date_sub('{{ execution_date | cst_ds }}',10) and dt<='{{ execution_date | cst_ds }}' ) cainiao_pro_config
on start_network.code=cainiao_pro_config.taking_code
and if(cast(detail.reback_apply_type_code as int)=4, aim_network.code,sign_network.code)=cainiao_pro_config.deliver_code
and date(detail.scantime)= date(cainiao_pro_config.dt)
and substr(detail.scantime, 12, 5) >= start_hm  and  substr(detail.scantime, 12, 5) <= end_hm
  left join jms_dim.dim_network_whole_massage start_center_code on detail.first_center_network_code = start_center_code.code
  left join jms_dim.dim_network_whole_massage sign_center_code on detail.end_center_network_code = sign_center_code.code
  --交件是否准点
  left join(
    select waybill,hand_over_intime from jms_dm.dm_waybill_collect_intime where dt >= date_add(date_sub('{{ execution_date | cst_ds }}',10),-2) and dt<='{{ execution_date | cst_ds }}'
  ) is_delivery_delay
  on detail.waybill_no = is_delivery_delay.waybill
  --出港是否及时
  left join(
  select billcode,is_timely,route_send_time from
    (select billcode,is_timely,route_send_time,row_number() over(partition by billcode order by send_scantime) as rnk
    from jms_dm.dm_cn_departure_transfer_timely_route_dt where dt >= date_add(date_sub('{{ execution_date | cst_ds }}',10),-1) and dt<='{{ execution_date | cst_ds }}'
    ) tt
    where tt.rnk=1
  ) is_out_delay
  on detail.waybill_no = is_out_delay.billcode
  left join (select waybill_id,field_name,row_number() over(partition by waybill_id order by input_time desc) rnk from jms_dwd.dwd_yl_oms_order_mark_expand_base_dt
  where dt between date_format(date_add(date_sub('{{ execution_date | cst_ds }}',10) ,-7), 'yyyy-MM-dd') and '{{ execution_date | cst_ds }}' and is_delete = 1
  ) waybill_field on waybill_field.waybill_id = detail.waybill_no
  and waybill_field.rnk = 1
  ) detail where rn = 1
  distribute by dt,abs(hash(waybill_no))%160;